package org.jeecg.modules.xl.home.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.xl.home.query.HomeQuery;
import org.jeecg.modules.xl.home.vo.*;

import java.util.List;

@Mapper
public interface HomeMapper extends BaseMapper {

    /**
     * 今日采购订单数据
     * @return
     */
    @Select("select count(t1.id) orderCnt,sum(total_amount) sum_amount " +
            ",sum(case when audit_state in (-1,0) then 1 else 0 end) un_audit_cnt,sum(case when audit_state in (1,2) then 1 else 0 end) audit_cnt " +
            ",sum(case when payment_state=1 then total_amount else 0 end) pay_amount " +
            ",sum(case when payment_state !=1 or payment_state is null then total_amount else 0 end) un_pay_amount " +
            "from xl_customer_orde t1 " +
            "where  t1.audit_state != '-1' and DATE(t1.create_time) = CURDATE()")
    HomeDayDataVO queryDayOrder();

    /**
     * 今日发货数据
     * @return
     */
    @Select("select  " +
            "sum(case when t2.shipment_state = 0 or t2.shipment_state is null then t1.order_quantity else 0 end) un_shipment_cnt " +
            ",sum(case when t2.shipment_state in (1,2) and t1.shipment_state = 1 then t1.order_quantity else 0 end) shipment_cnt " +
            "from xl_customer_order_detail t1 " +
            "join xl_customer_orde t2 on t1.order_id = t2.id and DATE(t2.create_time) = CURDATE() ")
    HomeDayDataVO queryDayShipment();

    /**
     * 今日售后数据
     * @return
     */
    @Select("select count(id) cnt, " +
            "sum(case when wo_status = 0 or wo_status is null then 1 else 0 end) un_aftersale_cnt " +
            ",sum(case when wo_status in (1,2)  then 1 else 0 end) aftersale_cnt " +
            "from xl_customer_aftersale " +
            "where DATE(create_time) = CURDATE()")
    HomeDayDataVO queryDayAftersale();

    /**
     * 订单数据 查询
     * @return
     */
    @Select("<script>" +
            "select count(t1.id) orderCnt,sum(total_amount) order_amount " +
            ",sum(case when payment_state=1 then total_amount else 0 end) pay_amount " +
            ",sum(case when payment_state !=1 or payment_state is null then total_amount else 0 end) un_pay_amount " +
            "from xl_customer_orde t1 " +
            "<where> t1.audit_state != '-1'  " +
            "<if test='query.orderDate_start != null and query.orderDate_end != null'> " +
            "   and DATE(t1.create_time) BETWEEN #{query.orderDate_start} and #{query.orderDate_end} " +
            "</if> " +
            "<if test='query.customerTypeCode != null and query.customerTypeCode != \"\"'> " +
            "   and t1.customer_type_code = #{query.customerTypeCode} " +
            "</if> " +
            "<if test='query.city != null and query.city != \"\"'> " +
            "   and t1.city like concat('%',#{query.city},'%') " +
            "</if> " +
            "<if test='query.storeCode != null and query.storeCode != \"\"'> " +
            "   and t1.store_code =  #{query.storeCode}  " +
            "</if> " +
            "</where> " +
            " </script> ")
    HomeDataVO queryOrderData(@Param("query") HomeQuery query);

    /**
     * 门店销售 查询 根据订单数量排序
     * @return
     */
    @Select("<script>" +
            "select t1.store_name,sum(t1.total_amount) amount,count(t1.id) cnt, " +
            "sum(case when payment_state !=1 or payment_state is null then 1 else 0 end) un_pay_cnt " +
            "from xl_customer_orde t1 " +
            "<where> t1.audit_state != '-1'  " +
            "<if test='query.orderDate_start != null and query.orderDate_end != null'> " +
            "   and DATE(t1.create_time) BETWEEN #{query.orderDate_start} and #{query.orderDate_end} " +
            "</if> " +
            "<if test='query.customerTypeCode != null and query.customerTypeCode != \"\"'> " +
            "   and t1.customer_type_code = #{query.customerTypeCode} " +
            "</if> " +
            "<if test='query.city != null and query.city != \"\"'> " +
            "   and t1.city like concat('%',#{query.city},'%') " +
            "</if> " +
            "<if test='query.storeCode != null and query.storeCode != \"\"'> " +
            "   and t1.store_code =  #{query.storeCode}  " +
            "</if> " +
            "</where> " +
            "group by t1.store_name " +
            "order by count(t1.id) desc " +
            " </script> ")
    IPage<HomeStoreListVO> getStoryList(@Param("page") Page page, @Param("query") HomeQuery query);

    /**
     * 商品排名 查询 根据订单数量排序
     * @return
     */
    @Select("<script>" +
            "select t1.upc_code as upc,t2.product_name " +
            ",sum(t3.total_amount) amount,count(t3.id) cnt, " +
            "count(DISTINCT t4.id) aftersale_cnt " +
            "from xl_customer_order_detail t1 " +
            "left join xl_product_info t2 on t1.product_id = t2.id " +
            "join xl_customer_orde t3 on t1.order_code = t3.order_code " +
            "left join xl_customer_aftersale t4 on t1.order_code = t4.order_code " +
            "<where> t3.audit_state != '-1'  " +
            "<if test='query.orderDate_start != null and query.orderDate_end != null'> " +
            "   and DATE(t3.create_time) BETWEEN #{query.orderDate_start} and #{query.orderDate_end} " +
            "</if> " +
            "<if test='query.customerTypeCode != null and query.customerTypeCode != \"\"'> " +
            "   and t3.customer_type_code = #{query.customerTypeCode} " +
            "</if> " +
            "<if test='query.city != null and query.city != \"\"'> " +
            "   and t3.city like concat('%',#{query.city},'%') " +
            "</if> " +
            "<if test='query.storeCode != null and query.storeCode != \"\"'> " +
            "   and t3.store_code =  #{query.storeCode}  " +
            "</if> " +
            "</where> " +
            "group by t1.upc_code,t2.product_name " +
            "order by count(t3.id) desc " +
            " </script> ")
    IPage<HomeProductListVO> getProductList(@Param("page") Page page, @Param("query") HomeQuery query);

    /**
     * 商品售后 查询 根据售后数量排序
     * @return
     */
    @Select("<script>" +
            "select t2.aftersale_type " +
            ",count(DISTINCT t2.id) aftersale_cnt  " +
            ",count(DISTINCT t1.product_id) sku_sum,sum(t1.aftersale_amount) aftersale_amount " +
            "from xl_customer_aftersale_detail t1  " +
            "join xl_customer_aftersale t2 on t1.workorder = t2.workorder " +
            "<where>  3=3   " +
            "<if test='query.orderDate_start != null and query.orderDate_end != null'> " +
            "   and DATE(t2.create_time) BETWEEN #{query.orderDate_start} and #{query.orderDate_end} " +
            "</if> " +
            "<if test='query.storeCode != null and query.storeCode != \"\"'> " +
            "   and t2.store_code =  #{query.storeCode}  " +
            "</if> " +

            "<if test='query.customerTypeCode != null or query.city != null ' > " +
                "   and exists(select 1 from xl_customer_orde co where co.order_code = t2.order_code " +
                "<if test='query.customerTypeCode != null and query.customerTypeCode != \"\"'> " +
                "   and co.customer_type_code = #{query.customerTypeCode} " +
                "</if> " +
                "<if test='query.city != null and query.city != \"\"'> " +
                "   and co.city like concat('%',#{query.city},'%') " +
                "</if> " +
                "  ) " +
            "</if> " +
            "</where> " +
            "group by t2.aftersale_type " +
            "order by count(t2.id) desc " +
            " </script> ")
    List<HomeAftersaleTypeListVO> getAftersaleTypeList(@Param("query") HomeQuery query);

    /**
     * 商品售后 查询 根据售后数量排序
     * @return
     */
    @Select("<script>" +
            "select t1.upc_code as upc,t3.product_name,t2.renson " +
            ",sum(t1.aftersale_quantity) aftersale_quantity,sum(t1.aftersale_amount) aftersale_amount " +
            "from xl_customer_aftersale_detail t1  " +
            "join xl_customer_aftersale t2 on t1.workorder = t2.workorder " +
            "left join xl_product_info t3 on t1.product_id = t3.id " +
            "<where>  3=3   " +
            "<if test='query.orderDate_start != null and query.orderDate_end != null'> " +
            "   and DATE(t2.create_time) BETWEEN #{query.orderDate_start} and #{query.orderDate_end} " +
            "</if> " +
            "<if test='query.storeCode != null and query.storeCode != \"\"'> " +
            "   and t2.store_code =  #{query.storeCode}  " +
            "</if> " +

            "<if test='query.customerTypeCode != null or query.city != null ' > " +
            "   and exists(select 1 from xl_customer_orde co where co.order_code = t2.order_code " +
                "<if test='query.customerTypeCode != null and query.customerTypeCode != \"\"'> " +
                "   and co.customer_type_code = #{query.customerTypeCode} " +
                "</if> " +
                "<if test='query.city != null and query.city != \"\"'> " +
                "   and co.city like concat('%',#{query.city},'%') " +
                "</if> " +
            "  ) " +
            "</if> " +
            "</where> " +
            "group by t1.upc_code,t3.product_name,t2.renson " +
            "order by sum(t1.aftersale_quantity) desc " +
            //默认取前5条
            " limit 5 " +
            " </script> ")
    List<HomeAftersaleListVO> getAftersaleList(@Param("query") HomeQuery query);
}
